---EXEC sp_create_update_fab_lot 'LDS273ACG04','LDJ22119.1','Running', 24, '3159', '06/01/2006', 'NSD-Mask-CED', '214/472', '09/30/2006','SILTERRA', NULL, NULL, NULL, NULL

ALTER proc sp_create_update_fab_lot (

@part_number 		varchar(50), 
@lot_number 		varchar(50), 
@lot_status 		varchar(50), 
@wafer_qty_in 		int, 
@po_number 			varchar(50),
@date_in 			datetime,
@lot_stage_name 	varchar(50),
@lot_stage_position varchar(50),
@expected_date_out 	datetime,
@vendor_name 		varchar(50),
@wafer_qty_out 		varchar(50), 
@date_out 			datetime,
@outLook_date 		datetime,
@wafer_Id 			varchar(100),
@ps_type			varchar(50),
@lot_Category		varchar(10)
)
as
begin
	declare @process_step_id 	int
	declare @location_id 		int
	declare @lot_id 		int
			
	if @date_in is null OR LTRIM( RTRIM(@DATE_IN)) = ''
	begin	
		set @date_in = CURRENT_TIMESTAMP
	end
	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end

	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name = 'FAB' and PS_Type = @ps_type and product.ProcessStep_ID = Process_Step.ProcessStep_ID
	select @location_id = Location_ID from Location where L_Type = 'Fab' and L_Vendor_Name = @vendor_name	
	
	if @process_step_id is null -- new Product or Product Name mismatch
	begin
		select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name = 'FAB' and PS_Type = @ps_type and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID
		if @process_step_id is null
		begin
			--IF @VENDOR_NAME = 'TSMC' OR @VENDOR_NAME = 'CHARTERED'
			--BEGIN
				IF @PROCESS_STEP_ID IS NULL
				BEGIN					
					SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'FAB' AND PS_TYPE = 'FAB OPTION' AND PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
					
					IF @PROCESS_STEP_ID IS NULL
					BEGIN
						SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'FAB' AND PS_TYPE = 'FAB OPTION' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID
					END	
				END
			--END
			IF @PROCESS_STEP_ID IS NULL
			BEGIN
				--For Discrepancy Lot Notification Alert
				if ( select count(*) from Notification_Alert_Table where part_number = @part_number and Lot_Number = @lot_number and Fab_Lot_Number = @lot_number and Vendor_Name = @vendor_name and Process_Type = @ps_type and Is_USER_Notified = 1 ) = 0 
				begin
					INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty,Wafer_id, Status,TimeStamp )
						VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, @ps_type,'Discrepancy Lot Notification', @wafer_qty_in, @wafer_Id, 'Open', CURRENT_TIMESTAMP )
				end
				
				update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
	
				return
			END
		END
	END
	
	select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
	
	if @lot_id is null -- Check if the lot exists
	begin -- Create Lot
		
		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Wafer_Qty_Out, Date_In, Date_Out,Stage_Name, Stage_Position,Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Drop_Out, Po_Number, Lot_category) 
			values (@process_step_id, @location_id, @lot_number,@lot_number,@lot_number,@wafer_qty_in, @wafer_qty_out,@date_in,@date_out,@lot_stage_name, @lot_stage_position, @lot_status, CURRENT_TIMESTAMP, 1 , 0, @po_number, @lot_Category )
		select @lot_id = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty,Wafer_id, Status,TimeStamp )
				VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, @ps_type ,'New Lot Notification', @wafer_qty_in, @wafer_Id, 'Open', CURRENT_TIMESTAMP )		
				
		insert into FAB_Lot(Lot_ID, FL_Expected_Date_Out, FL_OutLook_Date ,Wafer_Id, Timestamp ) values (@lot_id, @expected_date_out, @outLook_Date, @Wafer_Id, CURRENT_TIMESTAMP )
	end
	else
	begin 
		update Lot_Table set Lot_Current_Status = @lot_status, Wafer_Qty_In = @wafer_qty_in, Stage_Name = @lot_stage_name, Stage_Position = @lot_stage_position, Lot_Drop_Out = 0, PO_Number = @po_number, Lot_Category = @lot_Category where Lot_ID = @lot_id		
		
		update FAB_Lot set FL_Expected_Date_Out = @expected_date_out, FL_OutLook_Date = @outLook_Date, Timestamp=CURRENT_TIMESTAMP, Wafer_Id = @Wafer_Id where Lot_ID = @lot_id	
	end		
end


